spool 3_revertAlterPackageBody.log

CREATE OR REPLACE PACKAGE BODY CONNEG.PQ_ARCHIVO AS
  /************************************************************
  NOMBRE:     PQ_ARCHIVO
  PROPOSITO:  Agrupacion de funcionalidad para carga de archivos

  REVISIONES:
  Version      Fecha        Autor           Descripcion
  ---------  ----------  ---------------  ------------------------
  1.0        05/09/2011  Alexander Yong     Creación
  ***********************************************************/

  /**********************************************************
  Se abre el archivo
  **********************************************************/
  PROCEDURE P_ABRIR_ARCHIVO(P_TEXT_IO IN OUT UTL_FILE.FILE_TYPE,
                            P_RUTA    IN VARCHAR2,
                            P_NOMBRE  IN VARCHAR2,
                            P_ACCESO  IN VARCHAR2) IS
  BEGIN
    P_TEXT_IO := UTL_FILE.FOPEN(P_RUTA, P_NOMBRE, P_ACCESO);

  EXCEPTION
    WHEN OTHERS THEN
      P_TEXT_IO := NULL;
      RAISE_APPLICATION_ERROR(-20000,
                              'Unable to open ' || P_NOMBRE || SQLERRM);
  END P_ABRIR_ARCHIVO;

  /**********************************************************
  Lee linea por linea
  **********************************************************/
  FUNCTION F_LEER_LINEA(P_TEXT_IO IN UTL_FILE.FILE_TYPE) RETURN VARCHAR IS
    LV_BUFFER VARCHAR2(2000);
  BEGIN
    LV_BUFFER := NULL;
    UTL_FILE.GET_LINE(P_TEXT_IO, LV_BUFFER);
    RETURN LV_BUFFER;
  EXCEPTION
    WHEN OTHERS THEN
      LV_BUFFER := NULL;
      RETURN LV_BUFFER;
  END F_LEER_LINEA;

  /**********************************************************
  Genera la cadena de campos a ser insertados
  **********************************************************/
  FUNCTION F_GENERA_CAMPOS(AN_CODARCHIVO NUMBER) RETURN VARCHAR2 IS

    LC_CADENA_CAMPOS VARCHAR2(2000);
    LC_RESULTADO     VARCHAR2(3000);

    CURSOR C_ARCHIVOLAYOUT IS
      SELECT CAMPOTABLA
        FROM ARCHIVOLAYOUT
       WHERE CODIGOARCHIVO = AN_CODARCHIVO
         AND FLAGACTIVO = 1
       ORDER BY POSICION ASC;

  BEGIN
    LC_CADENA_CAMPOS := 'CODIGOSECUENCIAL,';
    FOR LR_LAYOUT IN C_ARCHIVOLAYOUT LOOP
      LC_CADENA_CAMPOS := LC_CADENA_CAMPOS || LR_LAYOUT.CAMPOTABLA || ',';
    END LOOP;

    LC_RESULTADO := '(' || LC_CADENA_CAMPOS || 'IDLOTE)';

    RETURN LC_RESULTADO;
  END F_GENERA_CAMPOS;

  /**********************************************************
  Genera la cadena de valores a ser insertados
  **********************************************************/
  FUNCTION F_GENERA_VALUES(AN_CODARCHIVO NUMBER,
                           AC_LINEA      VARCHAR2,
                           AN_IDLOTE     NUMBER) RETURN VARCHAR2 IS

    LC_CADENA_VALUES VARCHAR2(2000);
    LC_VALOR         VARCHAR2(200);
    LC_RESULTADO     VARCHAR2(3000);
    LN_POSICION      NUMBER;
    LC_SEQUENCE      VARCHAR2(100);
    LN_SECUENCIAL    NUMBER(8);
    LC_COMANDO       VARCHAR2(2000);

    CURSOR C_ARCHIVOLAYOUT IS
      SELECT LONGITUD, TIPODATO
        FROM ARCHIVOLAYOUT
       WHERE CODIGOARCHIVO = AN_CODARCHIVO
         AND FLAGACTIVO = 1
       ORDER BY POSICION ASC;

  BEGIN
    SELECT SEQUENCE
      INTO LC_SEQUENCE
      FROM ARCHIVO
     WHERE CODIGOARCHIVO = AN_CODARCHIVO;

    LC_COMANDO := 'SELECT ' || LC_SEQUENCE || '.NEXTVAL FROM DUAL';

    EXECUTE IMMEDIATE LC_COMANDO
      INTO LN_SECUENCIAL;

    LC_CADENA_VALUES := LN_SECUENCIAL || ',';
    LN_POSICION      := 1;
    FOR LR_LAYOUT IN C_ARCHIVOLAYOUT LOOP
      LC_VALOR    := SUBSTR(AC_LINEA, LN_POSICION, LR_LAYOUT.LONGITUD);
      LN_POSICION := LN_POSICION + LR_LAYOUT.LONGITUD;

      IF LR_LAYOUT.TIPODATO = 'C' THEN
        LC_VALOR := 'TRIM(''' || LC_VALOR || ''')';
      ELSIF LR_LAYOUT.TIPODATO = 'D' THEN
        LC_VALOR := 'to_date(' || '''' || LC_VALOR || '''' || ',' || '''' ||
                    'dd/mm/yyyy' || '''' || ')';
      END IF;

      LC_CADENA_VALUES := LC_CADENA_VALUES || LC_VALOR || ',';
    END LOOP;

    LC_RESULTADO := '(' || LC_CADENA_VALUES || AN_IDLOTE || ')';

    RETURN LC_RESULTADO;
  END F_GENERA_VALUES;



  FUNCTION F_GENERA_VALUES_UPDATE(AN_CODARCHIVO NUMBER,
                         AC_LINEA      VARCHAR2,
                         AN_IDLOTE     NUMBER) RETURN VARCHAR2 IS

    LC_CADENA_VALUES VARCHAR2(2000);
    LC_VALOR         VARCHAR2(200);
    LC_RESULTADO     VARCHAR2(3000);
    LN_POSICION      NUMBER;
    LC_SEQUENCE      VARCHAR2(100);
    LN_SECUENCIAL    NUMBER(8);
    LC_COMANDO       VARCHAR2(2000);
    LC_CONDICION     VARCHAR2(2000);
    LN_NOMBRECOLUMNA  VARCHAR2(1000);

    CURSOR C_ARCHIVOLAYOUT IS
      SELECT LONGITUD, TIPODATO,CAMPOTABLA,FLAGLLAVE
        FROM ARCHIVOLAYOUT
       WHERE CODIGOARCHIVO = AN_CODARCHIVO
         AND FLAGACTIVO = 1
       ORDER BY POSICION ASC;

  BEGIN
    SELECT SEQUENCE
      INTO LC_SEQUENCE
      FROM ARCHIVO
     WHERE CODIGOARCHIVO = AN_CODARCHIVO;

    LC_COMANDO := 'SELECT ' || LC_SEQUENCE || '.NEXTVAL FROM DUAL';
    LC_CONDICION :='';

    EXECUTE IMMEDIATE LC_COMANDO
      INTO LN_SECUENCIAL;

    LN_POSICION      := 1;
    FOR LR_LAYOUT IN C_ARCHIVOLAYOUT LOOP
      LC_VALOR    := SUBSTR(AC_LINEA, LN_POSICION, LR_LAYOUT.LONGITUD);
      LN_POSICION := LN_POSICION + LR_LAYOUT.LONGITUD;
      LN_NOMBRECOLUMNA:=LR_LAYOUT.CAMPOTABLA;
      IF LR_LAYOUT.TIPODATO = 'C' THEN
        LC_VALOR := 'TRIM(''' || LC_VALOR || ''')';
      ELSIF LR_LAYOUT.TIPODATO = 'D' THEN
        LC_VALOR := 'to_date(' || '''' || LC_VALOR || '''' || ',' || '''' ||
                    'dd/mm/yyyy' || '''' || ')';
      END IF;

      IF(LR_LAYOUT.FLAGLLAVE=1)THEN
        IF(LC_CONDICION IS NULL)THEN
          LC_CONDICION      :=  LN_NOMBRECOLUMNA||'='|| LC_VALOR ;
        ELSE
          LC_CONDICION      :=  LC_CONDICION||' AND '||LN_NOMBRECOLUMNA||'='|| LC_VALOR ;
        END IF;
      ELSE
        LC_CADENA_VALUES  := LC_CADENA_VALUES ||LN_NOMBRECOLUMNA||'='|| LC_VALOR || ',';
      END IF;
    END LOOP;
    LC_RESULTADO :=  LC_CADENA_VALUES ||'IDLOTE='|| AN_IDLOTE ||' WHERE '||LC_CONDICION ;
    RETURN LC_RESULTADO;
END F_GENERA_VALUES_UPDATE;

  /**********************************************************
  Genera los registros en la lotearchivo para el seguimiento de los lotes.
  **********************************************************/
  FUNCTION P_REGISTRAR_LOTEARCHIVO(AN_CODARCHIVO NUMBER, AC_NOMBARCH VARCHAR2)
    RETURN NUMBER
  IS
    LN_IDLOTE NUMBER(10);
  BEGIN
    SELECT MAX(IDLOTE) + 1 INTO LN_IDLOTE FROM LOTEARCHIVO;--3498

    IF LN_IDLOTE IS NULL THEN
      LN_IDLOTE := 1;
    END IF;

    INSERT INTO LOTEARCHIVO(IDLOTE, CODIGOARCHIVO, NOMBREARCHIVO, FECHAINICIO)
    VALUES (LN_IDLOTE, AN_CODARCHIVO, AC_NOMBARCH, SYSDATE);

    COMMIT;

    RETURN LN_IDLOTE;
  END P_REGISTRAR_LOTEARCHIVO;

  PROCEDURE P_ACTUALIZAR_LOTEARCHIVO(P_LOTEARCHIVO IN LOTEARCHIVO%ROWTYPE)
  IS
  BEGIN
    UPDATE LOTEARCHIVO
    SET    FECHAFIN          = SYSDATE,
           ESTADO            = P_LOTEARCHIVO.ESTADO,
           CANTIDADREGISTROS = P_LOTEARCHIVO.CANTIDADREGISTROS,
           CANTIDADCORRECTOS = P_LOTEARCHIVO.CANTIDADCORRECTOS,
           CANTIDADERRADOS   = P_LOTEARCHIVO.CANTIDADERRADOS
    WHERE  IDLOTE            = P_LOTEARCHIVO.IDLOTE;
  END;
  /**********************************************************
  Realiza todo el proceso de carga de archivos
  **********************************************************/
  PROCEDURE P_CARGAR_ARCHIVO(AN_CODARCHIVO NUMBER) IS

    P_TEXT_IO      UTL_FILE.FILE_TYPE;
    LN_CONTADOR    NUMBER DEFAULT 0;
    LC_NOMBARCHIVO VARCHAR2(200);
    LC_HOY         VARCHAR2(8);
    LC_LINEBUF     VARCHAR2(2000);
    LC_COMANDO     VARCHAR2(3500);
    LC_COMANDO_AUX VARCHAR2(3500);
    LC_CAMPOS      VARCHAR2(3000);
    LC_VALUES      VARCHAR2(3000);
    LN_IDLOTE      NUMBER(10);
    LN_CORRECTOS   NUMBER DEFAULT 0;
    LN_INCORRECTOS NUMBER DEFAULT 0;
    LC_MENSAJE     VARCHAR2(200);
    LC_CORREOS     ARCHIVO.CORREOELECTRONICO%TYPE;
    LC_FROM        VARCHAR2(100);
    L_INDICADOR_ROW VARCHAR2(1);

    CURSOR C_ARCHIVO IS
      SELECT NOMBREARCHIVO, RUTAENTRADA, TABLA, EXTENSION
        FROM ARCHIVO
       WHERE CODIGOARCHIVO = AN_CODARCHIVO;

  BEGIN
    LC_COMANDO := 'INSERT INTO <TABLA> <CAMPOS> VALUES <VALORES>';
    LC_CAMPOS  := F_GENERA_CAMPOS(AN_CODARCHIVO);

    FOR LR_ARCHIVO IN C_ARCHIVO LOOP
      SELECT TO_CHAR(SYSDATE, 'yymmdd') INTO LC_HOY FROM DUAL;
      /*LC_NOMBARCHIVO := LR_ARCHIVO.NOMBREARCHIVO || '_' || LC_HOY || '.' ||
                        LR_ARCHIVO.EXTENSION;*/

      LC_NOMBARCHIVO := LR_ARCHIVO.NOMBREARCHIVO || LC_HOY || '.' || LR_ARCHIVO.EXTENSION;
      --LC_NOMBARCHIVO := LR_ARCHIVO.NOMBREARCHIVO || '140410' || '.' || LR_ARCHIVO.EXTENSION;
      LN_IDLOTE      := P_REGISTRAR_LOTEARCHIVO(AN_CODARCHIVO,LC_NOMBARCHIVO);

      --ABRIMOS EL ARCHIVO
      BEGIN
        P_ABRIR_ARCHIVO(P_TEXT_IO,LR_ARCHIVO.RUTAENTRADA,LC_NOMBARCHIVO,'r');

        EXCEPTION
        WHEN OTHERS THEN
        BEGIN
          UPDATE LOTEARCHIVO SET FECHAFIN = SYSDATE, ESTADO = G_ESTADO_CARGA_PERDIDO WHERE IDLOTE = LN_IDLOTE;
          COMMIT;
          RETURN;
        END;
      END;

      LC_COMANDO := REPLACE(LC_COMANDO, '<TABLA>', LR_ARCHIVO.TABLA);
      LC_COMANDO := REPLACE(LC_COMANDO, '<CAMPOS>', LC_CAMPOS);
/*Lourdes Neira*/
  --    IF(LR_ARCHIVO.TABLA<>'ENTRADACUENTA')THEN
  --      EXECUTE IMMEDIATE 'DELETE FROM ' || LR_ARCHIVO.TABLA;
  --    END IF;
/*Lourdes Neira */
      --ACTUALIZAMOS EL ESTADO DEL ARCHIVO A EN PROCESO
      UPDATE LOTEARCHIVO SET ESTADO = G_ESTADO_CARGA_ENPROCESO WHERE IDLOTE = LN_IDLOTE;
      COMMIT;

      --REALIZAMOS LA LECTURA DE DATOS DEL ARCHIVO
      LOOP
        BEGIN
          LC_LINEBUF := F_LEER_LINEA(P_TEXT_IO);
          EXIT WHEN LC_LINEBUF IS NULL;

          IF(LR_ARCHIVO.TABLA='ENTRADACUENTA')THEN
                L_INDICADOR_ROW:=SUBSTR(LC_LINEBUF,0,1);
                IF(L_INDICADOR_ROW='N') THEN
                    LC_LINEBUF     := REPLACE(LC_LINEBUF, CHR(13), NULL);
                    LN_CONTADOR    := LN_CONTADOR + 1;
                    LC_VALUES      := F_GENERA_VALUES(AN_CODARCHIVO,
                                                  LC_LINEBUF,
                                                  LN_IDLOTE);
                    LC_COMANDO_AUX := REPLACE(LC_COMANDO, '<VALORES>', LC_VALUES);
                ELSIF(L_INDICADOR_ROW='M')THEN
                    LC_LINEBUF     := REPLACE(LC_LINEBUF, CHR(13), NULL);
                    LN_CONTADOR    := LN_CONTADOR + 1;
                    LC_VALUES      := F_GENERA_VALUES_UPDATE(AN_CODARCHIVO,
                                                  LC_LINEBUF,
                                                  LN_IDLOTE);

                    LC_COMANDO_AUX:=' UPDATE ENTRADACUENTA SET '||LC_VALUES;

                END IF;
          ELSE
                LC_LINEBUF     := REPLACE(LC_LINEBUF, CHR(13), NULL);
                LN_CONTADOR    := LN_CONTADOR + 1;
                LC_VALUES      := F_GENERA_VALUES(AN_CODARCHIVO,
                                                  LC_LINEBUF,
                                                  LN_IDLOTE);
                LC_COMANDO_AUX := REPLACE(LC_COMANDO, '<VALORES>', LC_VALUES);
          END IF;
          BEGIN
            dbms_output.put_line(LC_COMANDO_AUX);
            EXECUTE IMMEDIATE LC_COMANDO_AUX;
            LN_CORRECTOS := LN_CORRECTOS + 1;
          EXCEPTION
            WHEN OTHERS THEN
              LN_INCORRECTOS := LN_INCORRECTOS + 1;
              INSERT INTO ARCHIVO_LOG
              VALUES
                (LC_NOMBARCHIVO, LN_CONTADOR);
          END;
          IF MOD(LN_CONTADOR, 1000) = 0 THEN
            COMMIT;
          END IF;

        END;
      END LOOP;

      UPDATE LOTEARCHIVO
         SET FECHAFIN          = SYSDATE,
             CANTIDADREGISTROS = LN_CONTADOR,
             CANTIDADCORRECTOS = LN_CORRECTOS,
             CANTIDADERRADOS   = LN_INCORRECTOS,
             ESTADO            = G_ESTADO_CARGA_TERMINADO
       WHERE IDLOTE            = LN_IDLOTE;

      IF LN_INCORRECTOS > 0 THEN

        SELECT CORREOELECTRONICO
          INTO LC_CORREOS
          FROM ARCHIVO
         WHERE CODIGOARCHIVO = AN_CODARCHIVO;

        SELECT VALORALFANUMERICO
          INTO LC_FROM
          FROM PARAMETRO
         WHERE DESCRIPCION = 'FROM'
           AND CODIGOTIPOPARAMETRO =
               (SELECT CODIGOTIPOPARAMETRO
                  FROM TIPOPARAMETRO
                 WHERE CODIGOUNICO = 'PARAMETROS_CORREOS');

        LC_MENSAJE := 'Existen registros que no fueron cargados correctamente. Revisar la tabla archivo_log para el nombre de archivo ' ||
                      LC_NOMBARCHIVO;
      /*
        SP_SEND_MAIL(LC_FROM,
                                 LC_CORREOS,
                                 '',
                                 'Error - Carga de archivos',
                                 LC_MENSAJE);
      */
      END IF;

      COMMIT;

    END LOOP;
  END P_CARGAR_ARCHIVO;

/**********************************************************
  Genera query dinamico para la generación del archivo
  **********************************************************/
  FUNCTION F_GENERA_QUERY(AN_CODARCHIVO NUMBER, ln_idlote NUMBER) RETURN VARCHAR2 IS

    CURSOR C_ARCHIVOLAYOUT IS
      SELECT CAMPOTABLA, LONGITUD, TIPODATO
        FROM ARCHIVOLAYOUT
       WHERE CODIGOARCHIVO = AN_CODARCHIVO
         AND FLAGACTIVO = 1
       ORDER BY POSICION ASC;

    LC_QUERY VARCHAR2(5000);
    LC_CAMPO VARCHAR2(200);
    LC_TABLA VARCHAR2(100);

  BEGIN
    SELECT TABLA
      INTO LC_TABLA
      FROM ARCHIVO
     WHERE CODIGOARCHIVO = AN_CODARCHIVO;--salidacuenta

    LC_QUERY := 'SELECT ';

    FOR LR_ARCHIVOLAYOUT IN C_ARCHIVOLAYOUT LOOP
      IF LR_ARCHIVOLAYOUT.TIPODATO = 'C' THEN
        LC_CAMPO := 'RPAD(NVL(TO_CHAR(' || LR_ARCHIVOLAYOUT.CAMPOTABLA || '),'' ''),' ||
                    LR_ARCHIVOLAYOUT.LONGITUD || ',' || '''' || ' ' || '''' || ')';
      ELSIF LR_ARCHIVOLAYOUT.TIPODATO = 'N' THEN
        LC_CAMPO := 'LPAD(NVL(TO_CHAR(' || LR_ARCHIVOLAYOUT.CAMPOTABLA || '),''0''),' ||
                    LR_ARCHIVOLAYOUT.LONGITUD || ',' || '''' || ' ' || '''' || ')';
      ELSE
        LC_CAMPO := LR_ARCHIVOLAYOUT.CAMPOTABLA;
      END IF;
      LC_QUERY := LC_QUERY || LC_CAMPO || '||';
    END LOOP;

    LC_QUERY := RTRIM(LC_QUERY, '||');
    LC_QUERY := LC_QUERY || ' FROM ' || LC_TABLA || ' WHERE LOTE_ID = ' || ln_idlote;

    RETURN LC_QUERY;

  END F_GENERA_QUERY;

  /**********************************************************
  Genera archivo
  **********************************************************/
  PROCEDURE P_GENERAR_ARCHIVO(AN_CODARCHIVO IN NUMBER, P_LOTE_ID OUT INTEGER) IS

    ID                 INTEGER;
    RESULTADO          INTEGER;
    LC_QUERY           VARCHAR2(5000);
    LC_LINEA           VARCHAR(5000);
    LC_RUTA_ARCHIVO    VARCHAR2(2000);
    LC_NOM_ARCHIVO     VARCHAR2(2000);
    LB_ARCHIVO_ABIERTO BOOLEAN DEFAULT FALSE;
    ARCHIVO            UTL_FILE.FILE_TYPE;
    lc_proc_llenado    VARCHAR2(500);
    L_LOTEARCHIVO      LOTEARCHIVO%ROWTYPE;
    L_CANTIDAD         INTEGER := 0;
  BEGIN

    SELECT NOMBREARCHIVO || TO_CHAR(SYSDATE, 'yymmdd') || '.' ||
           EXTENSION,
           RUTASALIDA, proc_llenado
           INTO
           LC_NOM_ARCHIVO, LC_RUTA_ARCHIVO, lc_proc_llenado
    FROM ARCHIVO
    WHERE CODIGOARCHIVO = AN_CODARCHIVO;

    P_LOTE_ID := P_REGISTRAR_LOTEARCHIVO(AN_CODARCHIVO, LC_NOM_ARCHIVO);--3518,PEBT.BGFD.FIX.OPSALTAM.CTASPER.D1130626.TXT

    --insert into conneg.testmane (dato1) values(P_LOTE_ID);

    lc_proc_llenado := 'Begin '||lc_proc_llenado||'('||P_LOTE_ID||'); end;';--3506
    --insert into conneg.testmane (dato1) values(lc_proc_llenado);
    --EXECUTE IMMEDIATE lc_proc_llenado INTO ln_idlote;
    EXECUTE IMMEDIATE lc_proc_llenado;
    --insert into conneg.testmane (dato1) values('llego1');
    --ln_idlote:=2;
    LC_QUERY := F_GENERA_QUERY(AN_CODARCHIVO, P_LOTE_ID);

    ID := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(ID, LC_QUERY, DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN(ID, 1, LC_LINEA, 5000);
    RESULTADO := DBMS_SQL.EXECUTE(ID);

    IF NOT LB_ARCHIVO_ABIERTO THEN
          ARCHIVO            := UTL_FILE.FOPEN(LC_RUTA_ARCHIVO,LC_NOM_ARCHIVO,'w');
          LB_ARCHIVO_ABIERTO := TRUE;
    END IF;

    LOOP
      IF DBMS_SQL.FETCH_ROWS(ID) > 0 THEN
        DBMS_SQL.COLUMN_VALUE(ID, 1, LC_LINEA);
        LC_LINEA := LC_LINEA || CHR(13);
        UTL_FILE.PUT_LINE(ARCHIVO, LC_LINEA);
        L_CANTIDAD := L_CANTIDAD + 1;
      ELSE
        DBMS_SQL.CLOSE_CURSOR(ID);
        EXIT;
      END IF;
    END LOOP;

    IF LB_ARCHIVO_ABIERTO THEN
        UTL_FILE.FFLUSH(ARCHIVO);
      --UTL_FILE.FCLOSE(ARCHIVO);
    END IF;

    L_LOTEARCHIVO.IDLOTE            := P_LOTE_ID;
    L_LOTEARCHIVO.CANTIDADREGISTROS := L_CANTIDAD;
    L_LOTEARCHIVO.CANTIDADCORRECTOS := L_CANTIDAD;
    L_LOTEARCHIVO.CANTIDADERRADOS   := 0;
    L_LOTEARCHIVO.ESTADO            := G_ESTADO_CARGA_TERMINADO;

    P_ACTUALIZAR_LOTEARCHIVO(L_LOTEARCHIVO);--3490
    UTL_FILE.FCLOSE(ARCHIVO);
  END P_GENERAR_ARCHIVO;

  /**********************************************************
  Procedimiento principal
  **********************************************************/
  PROCEDURE P_ARCHIVO(AN_CODARCHIVO NUMBER) IS

    LN_TIPOARCHIVO NUMBER(1);
  BEGIN
    SELECT TIPOARCHIVO
      INTO LN_TIPOARCHIVO
      FROM ARCHIVO
     WHERE CODIGOARCHIVO = AN_CODARCHIVO;

    IF LN_TIPOARCHIVO = 1 THEN
      P_CARGAR_ARCHIVO(AN_CODARCHIVO);
    ELSIF LN_TIPOARCHIVO = 2 THEN
      --P_GENERAR_ARCHIVO(AN_CODARCHIVO,NULL);
      COMMIT;
    END IF;
  END P_ARCHIVO;

  /**********************************************************
  CARGA AUTOMATICA
  **********************************************************/

  PROCEDURE SP_CARGAR_ARCHIVO(P_ARCHIVO_ID INTEGER, P_CAPTURA OUT INTEGER)
  IS
    L_FECHA_ID VARCHAR2(12);
    L_EXISTE   INTEGER;
    L_TABLA    VARCHAR2(100);
  BEGIN
    P_CAPTURA := 0;

    --OBTENEMOS EL ID DE LA FECHA
    SELECT TO_CHAR(SYSDATE,'YYYYMMDD') || ARC.PROGRAMACION_HORA || ARC.PROGRAMACION_MINUTO, ARC.TABLA INTO L_FECHA_ID, L_TABLA
    FROM   ARCHIVO ARC
    WHERE  ARC.CODIGOARCHIVO = P_ARCHIVO_ID;

    --VERIFICAMOS SI EL PROCESO YA FUE EJECUTADO
    SELECT COUNT(1) INTO L_EXISTE FROM ARCHIVO_EJECUCION
    WHERE  FECHA_ID   = L_FECHA_ID
    AND    ARCHIVO_ID = P_ARCHIVO_ID;

    --SI EL PROCESO NO FUE EJECUTADO
    IF L_EXISTE = 0 THEN
      P_CAPTURA := 1;

      INSERT INTO ARCHIVO_EJECUCION(FECHA_ID, ARCHIVO_ID, USUARIO_CREACION, FECHA_CREACION)
      VALUES(L_FECHA_ID, P_ARCHIVO_ID, USER, SYSDATE);

      COMMIT;

      P_ARCHIVO(P_ARCHIVO_ID);

      IF L_TABLA = 'ENTRADACUENTA' THEN
        UPDATE ENTRADACUENTA
        SET    SALDODISPONIBLE     = NVL(SALDOCONTABLE,0) + NVL(LINEACREDITO,0) - NVL(RETENCION,0) - NVL(IMPAGO,0),
               SALDODISPONIBLE_CNG = NVL(SALDOCONTABLE,0) + NVL(LINEACREDITO,0) - NVL(RETENCION,0) - NVL(IMPAGO,0),
               SALDOCONTABLE_CNG   = SALDOCONTABLE;
        COMMIT;
      END IF;

      IF L_TABLA = 'ENTRADACLIENTE' THEN
        UPDATE ENTRADACLIENTE
        SET NOMBRE_COMPLETO = CASE WHEN CODIGOIDENTIFICACION = 'R' AND NOT NUMEROIDENTIFICACION LIKE '10%'
                              THEN NVL(NOMBRECLIENTE,'')||NVL(PRIMERAPELLIDO,'')||NVL(SEGUNDOAPELLIDO,'')
                              ELSE NVL(NOMBRECLIENTE,'')||' '||NVL(PRIMERAPELLIDO,'')||' '||NVL(SEGUNDOAPELLIDO,'') END;
        COMMIT;
      END IF;

    END IF;
  END;

  PROCEDURE SP_ARCHIVOS_CARGA (P_ARCHIVO OUT SYS_REFCURSOR)
  IS
  BEGIN

    OPEN P_ARCHIVO
    FOR
    SELECT ARC.CODIGOARCHIVO ARCHIVO_ID --PROGRAMACION_TIPO, PROGRAMACION_MES, PROGRAMACION_DIA
    FROM   ARCHIVO ARC
    WHERE  ARC.FLAGACTIVO = 1
    AND    ARC.PROGRAMACION_HORA || ARC.PROGRAMACION_MINUTO = TO_CHAR(SYSDATE, 'HH24MI') --HORA Y MINUTO
    AND    NVL(PROGRAMACION_MES, TO_CHAR(SYSDATE, 'MM'))    = TO_CHAR(SYSDATE, 'MM')     --MES (SI ES NULO ES CUALQUIER MES)
    AND    (CASE
            WHEN PROGRAMACION_DIA IS NULL THEN 1                                               --CUALQUIER DIA
            WHEN PROGRAMACION_TIPO = '01' AND PROGRAMACION_DIA = TO_CHAR(SYSDATE, 'DD') THEN 1 --1 DIA DEL MES
            WHEN PROGRAMACION_TIPO = '02' AND PROGRAMACION_DIA = TO_CHAR(SYSDATE, 'D')  THEN 1 --1 DIA DE LA SEMANA
            ELSE 0 END) = 1;
  END;

PROCEDURE SP_CARGAR_EXP_CUENTA(P_LOTE_ID IN INTEGER)--3498
IS
  L_NUMERO_FIC INTEGER;
  L_CODIGO_OPE VARCHAR2(100);
  L_FLAGHOST INTEGER := 0;
  P_CODIGO_OPE_HOST_CHEQUE NVARCHAR2(3);
  L_CODIGO_OPE_RET VARCHAR2(100);
BEGIN
   --insert into conneg.debugtb (dato1) values(P_LOTE_ID);
   SELECT SUBSTR(VALORALFANUMERICO, 1, 3) INTO L_CODIGO_OPE_RET  FROM PARAMETRO WHERE CODIGOPARAMETRO = 4202;
BEGIN
  SELECT SUBSTR(VALORALFANUMERICO, 1, 3) INTO L_CODIGO_OPE FROM PARAMETRO WHERE CODIGOPARAMETRO = 4201;--DEP
  EXCEPTION
    WHEN OTHERS THEN
      L_CODIGO_OPE := '   ';
END;
  --insert into conneg.testmane (dato1) values(L_CODIGO_OPE);
  --commit;
SELECT COUNT(1) INTO L_NUMERO_FIC FROM INICIOAPLICACION;--53

-- VERIFICAMOS SI EL CHECK DE LA OPERACION ESTA HABILIDADO
FOR transaccion IN (SELECT trans.FLAGENVIARHOST, trans.codigotransaccion
            from conneg.transaccion trans
            where trans.codigotransaccion in (3,5))
LOOP
IF transaccion.FLAGENVIARHOST = 1 THEN
--REGISTRAMOS LOS DEPOSITOS Y RETIROS DE EFECTIVO
INSERT INTO SalidaCuenta(CodigoSecuencial,INTERFACE,NUMERO_FIC,CCC,CENTRO_ORI,USERID_ORI,IMPORTE,DIVISA,OBSERVA,FILLER1,CODIGO,SIGNO_OPER,FILLER2,LOTE_ID,F_ANULA)
SELECT SQ_SALIDACUENTA.NEXTVAL,
     --'PC' INTERFACE,
       'HQ' INTERFACE,
       L_NUMERO_FIC  NUMERO_FIC,
       CODIGOENTIDAD || CODIGOOFICINA || NUMEROCUENTA CCC,
       OFICINAREGISTRO CENTRO_ORI,
       USUARIOREGISTRO USERID_ORI,
       FN_FORMATO_MONTO_HOST_CI_C(IMPORTETOTAL)    IMPORTE,
       CODIGODIVISA                        DIVISA,
       DESCRIPCIONOBSERVACION              OBSERVA,
       --' '                                 FILLER1,
       FN_OPERACION_HOST(CODIGOTRANSACCION)   FILLER1,
       --L_CODIGO_OPE CODIGO,
        FN_OPERACION_CODIGO(CODIGOTRANSACCION)   CODIGO,
       --(CASE WHEN CODIGOTRANSACCION = 3 THEN 'I' ELSE 'E' END) SIGNO_OPER,
       (CASE WHEN CODIGOTRANSACCION = 3 THEN 'I' ELSE 'R' END) SIGNO_OPER,
       --' ' FILLER2,
       '000000000'  FILLER2,
       P_LOTE_ID,
       FLAGEXTORNO F_ANULA
FROM   OPERACIONEFECTIVO
--WHERE  CODIGOTRANSACCION IN (3,5);
WHERE  CODIGOTRANSACCION = transaccion.codigotransaccion AND FLAGEXTORNO=0;
END IF;
END LOOP;

  --insert into conneg.testmane (dato1) values(L_CODIGO_OPE);
  --commit;
 --INICIO BLOQUE UNO
SELECT NVL(trans.FLAGENVIARHOST,0) INTO L_FLAGHOST
 from conneg.transaccion trans
 where trans.codigotransaccion = 6;

  SELECT VALORALFANUMERICO INTO P_CODIGO_OPE_HOST_CHEQUE FROM PARAMETRO WHERE CODIGOPARAMETRO = 5082 AND CODIGOTIPOPARAMETRO = 50;

IF L_FLAGHOST = 1 THEN
--REGISTRAMOS LOS PAGOS DE CHEQUES PROPIOS DEL BANCO
INSERT INTO SalidaCuenta(CodigoSecuencial,INTERFACE,NUMERO_FIC,CCC,CENTRO_ORI,USERID_ORI,IMPORTE,DIVISA,OBSERVA,FILLER1,CODIGO,SIGNO_OPER,FILLER2,LOTE_ID,F_ANULA)
SELECT SQ_SALIDACUENTA.NEXTVAL,
       --'PC' INTERFACE,
       'HQ' INTERFACE,
       L_NUMERO_FIC  NUMERO_FIC,
       OPE.CODIGOENTIDAD || OPE.CODIGOOFICINA || OPE.NUMEROCUENTA CCC,
       OPE.OFICINAREGISTRO CENTRO_ORI,
       OPE.USUARIOREGISTRO USERID_ORI,
       FN_FORMATO_MONTO_HOST_CI_C(OPE.IMPORTETOTAL)    IMPORTE,
       OPE.CODIGODIVISA                        DIVISA,
       CHQ.OBSERVACIONCHEQUE                   OBSERVA,
       --' '                                     FILLER1,
       P_CODIGO_OPE_HOST_CHEQUE                 FILLER1,
       --L_CODIGO_OPE CODIGO,
       L_CODIGO_OPE_RET   CODIGO,
       --'E' SIGNO_OPER,
       'R' SIGNO_OPER,
       --' ' FILLER2,
       OPE.NUMEROCHEQUE FILLER2,
       P_LOTE_ID,
       OPE.FLAGEXTORNO F_ANULA
FROM   OPERACIONCHEQUE OPE, CHEQUE CHQ
WHERE  OPE.CODIGOTRANSACCION = 6
AND    OPE.CODIGOOPERACION   = CHQ.CODIGOOPERACION
AND    OPE.FLAGEXTORNO=0;
END IF;
--fin bloq
--COMMIT;

END;

PROCEDURE SP_CARGAR_EXP_TRANSACCION(P_LOTE_ID IN INTEGER)
IS
  L_CODIGO_OPE_DEP VARCHAR2(100);
  L_CODIGO_OPE_RET VARCHAR2(100);
BEGIN

BEGIN
  SELECT SUBSTR(VALORALFANUMERICO, 1, 3) INTO L_CODIGO_OPE_DEP FROM PARAMETRO WHERE CODIGOPARAMETRO = 4201;
  SELECT SUBSTR(VALORALFANUMERICO, 1, 3) INTO L_CODIGO_OPE_RET FROM PARAMETRO WHERE CODIGOPARAMETRO = 4202;

  EXCEPTION
    WHEN OTHERS THEN
      L_CODIGO_OPE_DEP := '   ';
      L_CODIGO_OPE_RET := '   ';
END;
-- INICIO DEPOSITO EFECTIVO
INSERT INTO SALIDATRANSACCION  (
    SECUENCIAL_ID,
    INDICADORCLIENTE,
    TIPODOCORIGEN,
    NUMERODOCORIGEN,
    NUMEROCONTRATOORIGEN,
    TIPODOCDESTINO,
    NUMERODOCDESTINO,
    NUMEROCONTRATODESTINO,
    INDICADORFORMAPAGO,
    FECHAOPERACION,
    CODIGODIVISA,
    IMPORTEOPERACION,
    INDICADORAPLICACION,
    CODIGOTRANSACCION,
    TIPODOCBENEFICIARIO,
    NUMERODOCBENEFICIARIO,
    NOMBREBENEFICIARIO,
    DIRECCIONBENEFICIARIO,
    NOMBREORDENANTE,
    DIRECCIONORDENANTE,
    TIPODOCORDENANTE,
    NUMERODOCORDENANTE,
    NOMBRESOLICITANTE,
    DIRECCIONSOLICITANTE,
    TIPODOCSOLICITANTE,
    NUMERODOCSOLICITANTE,
    INDICADORPROCEDENCIA,
    INDICADORTIPOCAMBIO,
    INDICADORANULACION,
    NUMEROCHEQUE,
    CODIGOOPERACION,
    DESCRIPCIONREFERENCIA,
    INDICADORDIVISAREAL,
    ENTIDADOPERACION,
    CODIGOCENTRO,
    CODIGOUSUARIO,
    NUMEROTERMINAL,
    HORAACTUAL,
    ENTIDADEMPRESA,
    INDICADORORIGENDINERO,
    NOMBREBCOINTER,
    DIRECCIONBCOINTER,
    NUMERODOCBCOINTER,
    NUMEROCTABCOINTER,
    INDICADOROPCION,
    LOTE_ID,
    USUARIOREGISTRO,
    FECHAREGISTRO,
    USUARIOMODIFICACION,
    FECHAMODIFICACION,
    IMPORTETOTALCEROS
  )
SELECT
      SQ_SALIDATRANSACCION.NEXTVAL  SECUENCIAL_ID,
      'S'                           INDICADORCLIENTE,
      SIG.TIPO_DOCU_ORDE            TIPODOCORIGEN,
      SIG.NUMERO_DOCU_ORDE          NUMERODOCORIGEN,
      NULL                           NUMEROCONTRATOORIGEN,
      NULL                           TIPODOCDESTINO,
      NULL                           NUMERODOCDESTINO,
      OE.NUMEROCUENTA               NUMEROCONTRATODESTINO,
      'E'                           INDICADORFORMAPAGO,
      TO_CHAR(OE.FECHAREGISTRO,'DD/MM/YYYY') FECHAOPERACION,
      OE.CODIGODIVISA               CODIGODIVISA,
      FN_FORMATO_MONTO_HOST(OE.IMPORTETOTAL)               IMPORTEOPERACION,
--      'PC'                         INDICADORAPLICACION,
        'HQ'                         INDICADORAPLICACION,
      T.CODIGOTRANSACCIONHOST                        CODIGOTRANSACCION,
      SIG.TIPO_DOCU_BENE            TIPODOCBENEFICIARIO,
      SIG.NUMERO_DOCU_BENE          NUMERODOCBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_BENE,1,50)  NOMBREBENEFICIARIO,
      SUBSTR(SIG.DIRECCION_BENE,1,35) DIRECCIONBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_ORDE,1,50)  NOMBREORDENANTE,
      SUBSTR(SIG.DIRECCION_ORDE,1,35) DIRECCIONORDENANTE,
      SIG.TIPO_DOCU_ORDE            TIPODOCORDENANTE,
      SIG.NUMERO_DOCU_ORDE          NUMERODOCORDENANTE,
      SUBSTR(SIG.NOMBRE_SOLI,1,50)  NOMBRESOLICITANTE,
      SUBSTR(SIG.DIRECCION_SOLI,1,35) DIRECCIONSOLICITANTE,
      SIG.TIPO_DOCU_SOLI            TIPODOCSOLICITANTE,
      SIG.NUMERO_DOCU_SOLI          NUMERODOCSOLICITANTE,
      'B'                           INDICADORPROCEDENCIA,
      NULL                           INDICADORTIPOCAMBIO,
      NULL                           INDICADORANULACION,
      NULL                           NUMEROCHEQUE,
     -- L_CODIGO_OPE /*OE.CODIGOOPERACION*/ CODIGOOPERACION,
      L_CODIGO_OPE_DEP /*OE.CODIGOOPERACION*/ CODIGOOPERACION,
      SUBSTR(OE.DESCRIPCIONOBSERVACION,1,15) DESCRIPCIONREFERENCIA,
      OE.CODIGODIVISA               INDICADORDIVISAREAL,
      '0011'                        ENTIDADOPERACION,
      OE.OFICINAREGISTRO            CODIGOCENTRO,
      OE.USUARIOREGISTRO            CODIGOUSUARIO,
      OE.TERMINALREGISTRO           NUMEROTERMINAL,
      F_HORA_SERVIDOR_HOST(OE.FECHAREGISTRO) HORAACTUAL,
      NULL                           ENTIDADEMPRESA,
      SIG.ORIGEN                    INDICADORORIGENDINERO,
      NULL                           NOMBREBCOINTER,
      NULL                           DIRECCIONBCOINTER,
      NULL                           NUMERODOCBCOINTER,
      NULL                           NUMEROCTABCOINTER,
      NULL                           INDICADOROPCION,
      P_LOTE_ID                               LOTE_ID,
      OE.USUARIOREGISTRO            USUARIOREGISTRO,
      OE.FECHAREGISTRO            FECHAREGISTRO,
      OE.USUARIOMODIFICACION        USUARIOMODIFICACION,
      OE.FECHAMODIFICACION          FECHAMODIFICACION,
      FN_FORMATO_MONTO_HOST_CI(OE.IMPORTETOTAL)  IMPORTETOTALCEROS
      FROM OPERACIONEFECTIVO OE
      INNER JOIN   OPERACION_SIGNIFICATIVA SIG ON (SIG.CODIGOOPERACION = OE.CODIGOOPERACION)
      INNER JOIN TRANSACCION  T ON(T.CODIGOTRANSACCION=OE.CODIGOTRANSACCION)
      WHERE OE.CODIGOTRANSACCION=3
      AND OE.flagextorno=0;
-- FIN DEPOSITO EFECTIVO


-- INICIO RETIRO EFECTIVO
INSERT INTO SALIDATRANSACCION  (
    SECUENCIAL_ID,
    INDICADORCLIENTE,
    TIPODOCORIGEN,
    NUMERODOCORIGEN,
    NUMEROCONTRATOORIGEN,
    TIPODOCDESTINO,
    NUMERODOCDESTINO,
    NUMEROCONTRATODESTINO,
    INDICADORFORMAPAGO,
    FECHAOPERACION,
    CODIGODIVISA,
    IMPORTEOPERACION,
    INDICADORAPLICACION,
    CODIGOTRANSACCION,
    TIPODOCBENEFICIARIO,
    NUMERODOCBENEFICIARIO,
    NOMBREBENEFICIARIO,
    DIRECCIONBENEFICIARIO,
    NOMBREORDENANTE,
    DIRECCIONORDENANTE,
    TIPODOCORDENANTE,
    NUMERODOCORDENANTE,
    NOMBRESOLICITANTE,
    DIRECCIONSOLICITANTE,
    TIPODOCSOLICITANTE,
    NUMERODOCSOLICITANTE,
    INDICADORPROCEDENCIA,
    INDICADORTIPOCAMBIO,
    INDICADORANULACION,
    NUMEROCHEQUE,
    CODIGOOPERACION,
    DESCRIPCIONREFERENCIA,
    INDICADORDIVISAREAL,
    ENTIDADOPERACION,
    CODIGOCENTRO,
    CODIGOUSUARIO,
    NUMEROTERMINAL,
    HORAACTUAL,
    ENTIDADEMPRESA,
    INDICADORORIGENDINERO,
    NOMBREBCOINTER,
    DIRECCIONBCOINTER,
    NUMERODOCBCOINTER,
    NUMEROCTABCOINTER,
    INDICADOROPCION,
    LOTE_ID,
    USUARIOREGISTRO,
    FECHAREGISTRO,
    USUARIOMODIFICACION,
    FECHAMODIFICACION,
    IMPORTETOTALCEROS
  )
SELECT
      SQ_SALIDATRANSACCION.NEXTVAL  SECUENCIAL_ID,
      'S'                           INDICADORCLIENTE,
      NULL                           TIPODOCORIGEN,
      NULL                          NUMERODOCORIGEN,
      OE.NUMEROCUENTA               NUMEROCONTRATOORIGEN,
      SIG.TIPO_DOCU_BENE            TIPODOCDESTINO,
      SIG.NUMERO_DOCU_BENE               NUMERODOCDESTINO,
      NULL                           NUMEROCONTRATODESTINO,
      'E'                           INDICADORFORMAPAGO,
      TO_CHAR(OE.FECHAREGISTRO,'DD/MM/YYYY')              FECHAOPERACION,
      OE.CODIGODIVISA               CODIGODIVISA,
      FN_FORMATO_MONTO_HOST(OE.IMPORTETOTAL)               IMPORTEOPERACION,
      --'PC'                         INDICADORAPLICACION,
      'HQ'                         INDICADORAPLICACION,
      T.CODIGOTRANSACCIONHOST                        CODIGOTRANSACCION,
      SIG.TIPO_DOCU_BENE            TIPODOCBENEFICIARIO,
      SIG.NUMERO_DOCU_BENE          NUMERODOCBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_BENE,1,50)    NOMBREBENEFICIARIO,
      SUBSTR(SIG.DIRECCION_BENE,1,35) DIRECCIONBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_ORDE,1,50)    NOMBREORDENANTE,
      SUBSTR(SIG.DIRECCION_ORDE,1,35) DIRECCIONORDENANTE,
      SIG.TIPO_DOCU_ORDE            TIPODOCORDENANTE,
      SIG.NUMERO_DOCU_ORDE          NUMERODOCORDENANTE,
      SUBSTR(SIG.NOMBRE_SOLI,1,50)  NOMBRESOLICITANTE,
      SUBSTR(SIG.DIRECCION_SOLI,1,35) DIRECCIONSOLICITANTE,
      SIG.TIPO_DOCU_SOLI            TIPODOCSOLICITANTE,
      SIG.NUMERO_DOCU_SOLI          NUMERODOCSOLICITANTE,
      'B'                           INDICADORPROCEDENCIA,
      NULL                           INDICADORTIPOCAMBIO,
      NULL                           INDICADORANULACION,
      NULL                           NUMEROCHEQUE,
     --L_CODIGO_OPE               CODIGOOPERACION,
      L_CODIGO_OPE_RET            CODIGOOPERACION,
      SUBSTR(OE.DESCRIPCIONOBSERVACION,1,15) DESCRIPCIONREFERENCIA,
      OE.CODIGODIVISA               INDICADORDIVISAREAL,
      '0011'                        ENTIDADOPERACION,
      OE.OFICINAREGISTRO            CODIGOCENTRO,
      OE.USUARIOREGISTRO            CODIGOUSUARIO,
      OE.TERMINALREGISTRO           NUMEROTERMINAL,
      F_HORA_SERVIDOR_HOST(OE.FECHAREGISTRO)              HORAACTUAL,
      NULL                           ENTIDADEMPRESA,
      SIG.ORIGEN                    INDICADORORIGENDINERO,
      NULL                           NOMBREBCOINTER,
      NULL                           DIRECCIONBCOINTER,
      NULL                           NUMERODOCBCOINTER,
      NULL                           NUMEROCTABCOINTER,
      NULL                           INDICADOROPCION,
      P_LOTE_ID                           LOTE_ID,
      OE.USUARIOREGISTRO            USUARIOREGISTRO,
      OE.FECHAREGISTRO            FECHAREGISTRO,
      OE.USUARIOMODIFICACION        USUARIOMODIFICACION,
      OE.FECHAMODIFICACION          FECHAMODIFICACION,
      FN_FORMATO_MONTO_HOST_CI(OE.IMPORTETOTAL)  IMPORTETOTALCEROS
      FROM OPERACIONEFECTIVO OE
      INNER JOIN   OPERACION_SIGNIFICATIVA SIG ON (SIG.CODIGOOPERACION = OE.CODIGOOPERACION)
      INNER JOIN TRANSACCION  T ON(T.CODIGOTRANSACCION=OE.CODIGOTRANSACCION)
      WHERE OE.CODIGOTRANSACCION='5'
      AND OE.flagextorno=0;
-- FIN RETIRO EFECTIVO



-- INICIO PAGO A CUENTA TAJETA DE CREDITO
INSERT INTO SALIDATRANSACCION  (
    SECUENCIAL_ID,
    INDICADORCLIENTE,
    TIPODOCORIGEN,
    NUMERODOCORIGEN,
    NUMEROCONTRATOORIGEN,
    TIPODOCDESTINO,
    NUMERODOCDESTINO,
    NUMEROCONTRATODESTINO,
    INDICADORFORMAPAGO,
    FECHAOPERACION,
    CODIGODIVISA,
    IMPORTEOPERACION,
    INDICADORAPLICACION,
    CODIGOTRANSACCION,
    TIPODOCBENEFICIARIO,
    NUMERODOCBENEFICIARIO,
    NOMBREBENEFICIARIO,
    DIRECCIONBENEFICIARIO,
    NOMBREORDENANTE,
    DIRECCIONORDENANTE,
    TIPODOCORDENANTE,
    NUMERODOCORDENANTE,
    NOMBRESOLICITANTE,
    DIRECCIONSOLICITANTE,
    TIPODOCSOLICITANTE,
    NUMERODOCSOLICITANTE,
    INDICADORPROCEDENCIA,
    INDICADORTIPOCAMBIO,
    INDICADORANULACION,
    NUMEROCHEQUE,
    CODIGOOPERACION,
    DESCRIPCIONREFERENCIA,
    INDICADORDIVISAREAL,
    ENTIDADOPERACION,
    CODIGOCENTRO,
    CODIGOUSUARIO,
    NUMEROTERMINAL,
    HORAACTUAL,
    ENTIDADEMPRESA,
    INDICADORORIGENDINERO,
    NOMBREBCOINTER,
    DIRECCIONBCOINTER,
    NUMERODOCBCOINTER,
    NUMEROCTABCOINTER,
    INDICADOROPCION,
    LOTE_ID,
    USUARIOREGISTRO,
    FECHAREGISTRO,
    USUARIOMODIFICACION,
    FECHAMODIFICACION,
    IMPORTETOTALCEROS
  )
SELECT
      SQ_SALIDATRANSACCION.NEXTVAL  SECUENCIAL_ID,
      'S'                           INDICADORCLIENTE,
      SIG.TIPO_DOCU_ORDE            TIPODOCORIGEN,
      SIG.NUMERO_DOCU_ORDE          NUMERODOCORIGEN,
      NULL                           NUMEROCONTRATOORIGEN,
      NULL                           TIPODOCDESTINO,
      NULL                           NUMERODOCDESTINO,
      OE.NUMEROCUENTA               NUMEROCONTRATODESTINO,
      'E'                           INDICADORFORMAPAGO,
      TO_CHAR(OE.FECHAREGISTRO,'DD/MM/YYYY')              FECHAOPERACION,
      OE.CODIGODIVISA               CODIGODIVISA,
      FN_FORMATO_MONTO_HOST(OE.IMPORTETOTAL)               IMPORTEOPERACION,
      --'PC'                         INDICADORAPLICACION,
      'HQ'                         INDICADORAPLICACION,
      T.CODIGOTRANSACCIONHOST                        CODIGOTRANSACCION,
      SIG.TIPO_DOCU_BENE            TIPODOCBENEFICIARIO,
      SIG.NUMERO_DOCU_BENE          NUMERODOCBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_BENE,1,50)    NOMBREBENEFICIARIO,
      SUBSTR(SIG.DIRECCION_BENE,1,35) DIRECCIONBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_ORDE,1,50)    NOMBREORDENANTE,
      SUBSTR(SIG.DIRECCION_ORDE,1,35) DIRECCIONORDENANTE,
      SIG.TIPO_DOCU_ORDE            TIPODOCORDENANTE,
      SIG.NUMERO_DOCU_ORDE          NUMERODOCORDENANTE,
      SUBSTR(SIG.NOMBRE_SOLI,1,50)  NOMBRESOLICITANTE,
      SUBSTR(SIG.DIRECCION_SOLI,1,35) DIRECCIONSOLICITANTE,
      SIG.TIPO_DOCU_SOLI            TIPODOCSOLICITANTE,
      SIG.NUMERO_DOCU_SOLI          NUMERODOCSOLICITANTE,
      'B'                           INDICADORPROCEDENCIA,
      NULL                           INDICADORTIPOCAMBIO,
      NULL                           INDICADORANULACION,
      NULL                           NUMEROCHEQUE,
      --L_CODIGO_OPE            CODIGOOPERACION,
      L_CODIGO_OPE_DEP          CODIGOOPERACION,
      SUBSTR(OE.DESCRIPCIONOBSERVACION,1,15) DESCRIPCIONREFERENCIA,
      OE.CODIGODIVISA               INDICADORDIVISAREAL,
      '0011'                        ENTIDADOPERACION,
      OE.OFICINAREGISTRO            CODIGOCENTRO,
      OE.USUARIOREGISTRO            CODIGOUSUARIO,
      OE.TERMINALREGISTRO           NUMEROTERMINAL,
      F_HORA_SERVIDOR_HOST(OE.FECHAREGISTRO)              HORAACTUAL,
      NULL                           ENTIDADEMPRESA,
      SIG.ORIGEN                    INDICADORORIGENDINERO,
      NULL                           NOMBREBCOINTER,
      NULL                           DIRECCIONBCOINTER,
      NULL                           NUMERODOCBCOINTER,
      NULL                           NUMEROCTABCOINTER,
      NULL                           INDICADOROPCION,
      P_LOTE_ID                     LOTE_ID,
      OE.USUARIOREGISTRO            USUARIOREGISTRO,
      OE.FECHAREGISTRO            FECHAREGISTRO,
      OE.USUARIOMODIFICACION        USUARIOMODIFICACION,
      OE.FECHAMODIFICACION          FECHAMODIFICACION,
       FN_FORMATO_MONTO_HOST_CI(OE.IMPORTETOTAL)  IMPORTETOTALCEROS
      FROM OPERACIONEFECTIVO OE
      INNER JOIN   OPERACION_SIGNIFICATIVA SIG ON (SIG.CODIGOOPERACION = OE.CODIGOOPERACION)
      INNER JOIN TRANSACCION  T ON(T.CODIGOTRANSACCION=OE.CODIGOTRANSACCION)
      WHERE OE.CODIGOTRANSACCION='1'
      AND OE.flagextorno=0;
-- FIN PAGO A CUENTA TAJETA DE CREDITO


-- DEPOSITO CHEQUE
INSERT INTO SALIDATRANSACCION  (
    SECUENCIAL_ID,
    INDICADORCLIENTE,
    TIPODOCORIGEN,
    NUMERODOCORIGEN,
    NUMEROCONTRATOORIGEN,
    TIPODOCDESTINO,
    NUMERODOCDESTINO,
    NUMEROCONTRATODESTINO,
    INDICADORFORMAPAGO,
    FECHAOPERACION,
    CODIGODIVISA,
    IMPORTEOPERACION,
    INDICADORAPLICACION,
    CODIGOTRANSACCION,
    TIPODOCBENEFICIARIO,
    NUMERODOCBENEFICIARIO,
    NOMBREBENEFICIARIO,
    DIRECCIONBENEFICIARIO,
    NOMBREORDENANTE,
    DIRECCIONORDENANTE,
    TIPODOCORDENANTE,
    NUMERODOCORDENANTE,
    NOMBRESOLICITANTE,
    DIRECCIONSOLICITANTE,
    TIPODOCSOLICITANTE,
    NUMERODOCSOLICITANTE,
    INDICADORPROCEDENCIA,
    INDICADORTIPOCAMBIO,
    INDICADORANULACION,
    NUMEROCHEQUE,
    CODIGOOPERACION,
    DESCRIPCIONREFERENCIA,
    INDICADORDIVISAREAL,
    ENTIDADOPERACION,
    CODIGOCENTRO,
    CODIGOUSUARIO,
    NUMEROTERMINAL,
    HORAACTUAL,
    ENTIDADEMPRESA,
    INDICADORORIGENDINERO,
    NOMBREBCOINTER,
    DIRECCIONBCOINTER,
    NUMERODOCBCOINTER,
    NUMEROCTABCOINTER,
    INDICADOROPCION,
    LOTE_ID,
    USUARIOREGISTRO,
    FECHAREGISTRO,
    USUARIOMODIFICACION,
    FECHAMODIFICACION,
    IMPORTETOTALCEROS
  )
SELECT
      SQ_SALIDATRANSACCION.NEXTVAL  SECUENCIAL_ID,
      /*CASE WHEN EC.NUMEROCLIENTE IS NULL THEN 'N' ELSE 'S' END INDICADORCLIENTE,*/
      'S'                           INDICADORCLIENTE,
      NULL                          TIPODOCORIGEN,
      NULL                          NUMERODOCORIGEN,
      NULL                          NUMEROCONTRATOORIGEN,
      NULL                          TIPODOCDESTINO,
      NULL                          NUMERODOCDESTINO,
      OC.NUMEROCUENTA               NUMEROCONTRATODESTINO,
      /*SIG.TIPO_DOCU_BENE            TIPODOCDESTINO,
      SIG.NUMERO_DOCU_BENE          NUMERODOCDESTINO,
      NULL               NUMEROCONTRATODESTINO,*/
      'Q'                           INDICADORFORMAPAGO,
      TO_CHAR(OC.FECHAREGISTRO,'DD/MM/YYYY')              FECHAOPERACION,
      OC.CODIGODIVISA               CODIGODIVISA,
      FN_FORMATO_MONTO_HOST(OC.IMPORTETOTAL)               IMPORTEOPERACION,
      --'PC'                          INDICADORAPLICACION,
      'HQ'                          INDICADORAPLICACION,
      T.CODIGOTRANSACCIONHOST                        CODIGOTRANSACCION,
      SIG.TIPO_DOCU_BENE            TIPODOCBENEFICIARIO,
      SIG.NUMERO_DOCU_BENE          NUMERODOCBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_BENE,1,50)               NOMBREBENEFICIARIO,
      SUBSTR(SIG.DIRECCION_BENE,1,35)            DIRECCIONBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_ORDE,1,50)               NOMBREORDENANTE,
      SUBSTR(SIG.DIRECCION_ORDE,1,35)            DIRECCIONORDENANTE,
      SIG.TIPO_DOCU_ORDE            TIPODOCORDENANTE,
      SIG.NUMERO_DOCU_ORDE          NUMERODOCORDENANTE,
      SUBSTR(SIG.NOMBRE_SOLI,1,50)               NOMBRESOLICITANTE,
      SUBSTR(SIG.DIRECCION_SOLI,1,35)            DIRECCIONSOLICITANTE,
      SIG.TIPO_DOCU_SOLI            TIPODOCSOLICITANTE,
      SIG.NUMERO_DOCU_SOLI          NUMERODOCSOLICITANTE,
      'B'                           INDICADORPROCEDENCIA,
      NULL                           INDICADORTIPOCAMBIO,
      NULL                           INDICADORANULACION,
      CH.NUMEROCHEQUE               NUMEROCHEQUE,
      --L_CODIGO_OPE                  CODIGOOPERACION,
      L_CODIGO_OPE_DEP                CODIGOOPERACION,
      NULL                          DESCRIPCIONREFERENCIA,
      OC.CODIGODIVISA               INDICADORDIVISAREAL,
      '0011'                        ENTIDADOPERACION,
      OC.OFICINAREGISTRO            CODIGOCENTRO,
      OC.USUARIOREGISTRO            CODIGOUSUARIO,
      OC.TERMINALREGISTRO           NUMEROTERMINAL,
      F_HORA_SERVIDOR_HOST(OC.FECHAREGISTRO)             HORAACTUAL,
      NULL                           ENTIDADEMPRESA,
      SIG.ORIGEN                    INDICADORORIGENDINERO,
      NULL                           NOMBREBCOINTER,
      NULL                           DIRECCIONBCOINTER,
      NULL                           NUMERODOCBCOINTER,
      NULL                           NUMEROCTABCOINTER,
      NULL                           INDICADOROPCION,
      P_LOTE_ID                     LOTE_ID,
      OC.USUARIOREGISTRO            USUARIOREGISTRO,
      OC.FECHAREGISTRO            FECHAREGISTRO,
      OC.USUARIOMODIFICACION        USUARIOMODIFICACION,
      OC.FECHAMODIFICACION          FECHAMODIFICACION,
      FN_FORMATO_MONTO_HOST_CI(OC.IMPORTETOTAL)  IMPORTETOTALCEROS
      FROM OPERACIONCHEQUE OC
      INNER JOIN   OPERACION_SIGNIFICATIVA SIG ON (SIG.CODIGOOPERACION = OC.CODIGOOPERACION)
      INNER JOIN CHEQUE CH                  ON(CH.CODIGOOPERACION=OC.CODIGOOPERACION)
      LEFT JOIN   ENTRADACLIENTE EC ON(EC.CODIGOIDENTIFICACION=SIG.TIPO_DOCU_BENE AND SIG.NUMERO_DOCU_BENE=EC.NUMEROIDENTIFICACION||NVL(EC.DIGITOIDENTIFICACION,''))
      INNER JOIN TRANSACCION  T ON(T.CODIGOTRANSACCION=OC.CODIGOTRANSACCION)
      WHERE OC.CODIGOTRANSACCION='4'
      AND OC.flagextorno=0;
-- FIN DEPOSITO CHEQUE

-- PAGO CHEQUE
INSERT INTO SALIDATRANSACCION  (
    SECUENCIAL_ID,
    INDICADORCLIENTE,
    TIPODOCORIGEN,
    NUMERODOCORIGEN,
    NUMEROCONTRATOORIGEN,
    TIPODOCDESTINO,
    NUMERODOCDESTINO,
    NUMEROCONTRATODESTINO,
    INDICADORFORMAPAGO,
    FECHAOPERACION,
    CODIGODIVISA,
    IMPORTEOPERACION,
    INDICADORAPLICACION,
    CODIGOTRANSACCION,
    TIPODOCBENEFICIARIO,
    NUMERODOCBENEFICIARIO,
    NOMBREBENEFICIARIO,
    DIRECCIONBENEFICIARIO,
    NOMBREORDENANTE,
    DIRECCIONORDENANTE,
    TIPODOCORDENANTE,
    NUMERODOCORDENANTE,
    NOMBRESOLICITANTE,
    DIRECCIONSOLICITANTE,
    TIPODOCSOLICITANTE,
    NUMERODOCSOLICITANTE,
    INDICADORPROCEDENCIA,
    INDICADORTIPOCAMBIO,
    INDICADORANULACION,
    NUMEROCHEQUE,
    CODIGOOPERACION,
    DESCRIPCIONREFERENCIA,
    INDICADORDIVISAREAL,
    ENTIDADOPERACION,
    CODIGOCENTRO,
    CODIGOUSUARIO,
    NUMEROTERMINAL,
    HORAACTUAL,
    ENTIDADEMPRESA,
    INDICADORORIGENDINERO,
    NOMBREBCOINTER,
    DIRECCIONBCOINTER,
    NUMERODOCBCOINTER,
    NUMEROCTABCOINTER,
    INDICADOROPCION,
    LOTE_ID,
    USUARIOREGISTRO,
    FECHAREGISTRO,
    USUARIOMODIFICACION,
    FECHAMODIFICACION,
    IMPORTETOTALCEROS
  )
SELECT
      SQ_SALIDATRANSACCION.NEXTVAL  SECUENCIAL_ID,
      CASE WHEN EC.NUMEROCLIENTE IS NULL THEN 'N' ELSE 'S' END INDICADORCLIENTE,
      NULL                           TIPODOCORIGEN,
      NULL                           NUMERODOCORIGEN,
      OC.NUMEROCUENTA               NUMEROCONTRATOORIGEN,
      SIG.TIPO_DOCU_BENE            TIPODOCDESTINO,
      SIG.NUMERO_DOCU_BENE          NUMERODOCDESTINO,
      NULL                          NUMEROCONTRATODESTINO,
      'Q'                           INDICADORFORMAPAGO,
      TO_CHAR(OC.FECHAREGISTRO,'DD/MM/YYYY')              FECHAOPERACION,
      OC.CODIGODIVISA               CODIGODIVISA,
      FN_FORMATO_MONTO_HOST(OC.IMPORTETOTAL)               IMPORTEOPERACION,
      --'PC'                          INDICADORAPLICACION,
      'HQ'                          INDICADORAPLICACION,
      T.CODIGOTRANSACCIONHOST                        CODIGOTRANSACCION,
      SIG.TIPO_DOCU_BENE            TIPODOCBENEFICIARIO,
      SIG.NUMERO_DOCU_BENE          NUMERODOCBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_BENE,1,50)               NOMBREBENEFICIARIO,
      SUBSTR(SIG.DIRECCION_BENE,1,35)            DIRECCIONBENEFICIARIO,
      SUBSTR(SIG.NOMBRE_ORDE,1,50)               NOMBREORDENANTE,
      SUBSTR(SIG.DIRECCION_ORDE,1,35)            DIRECCIONORDENANTE,
      SIG.TIPO_DOCU_ORDE            TIPODOCORDENANTE,
      SIG.NUMERO_DOCU_ORDE          NUMERODOCORDENANTE,
      SUBSTR(SIG.NOMBRE_SOLI,1,50)               NOMBRESOLICITANTE,
      SUBSTR(SIG.DIRECCION_SOLI,1,35)            DIRECCIONSOLICITANTE,
      SIG.TIPO_DOCU_SOLI            TIPODOCSOLICITANTE,
      SIG.NUMERO_DOCU_SOLI          NUMERODOCSOLICITANTE,
      'B'                           INDICADORPROCEDENCIA,
      NULL                           INDICADORTIPOCAMBIO,
      NULL                           INDICADORANULACION,
      CH.NUMEROCHEQUE               NUMEROCHEQUE,
      --L_CODIGO_OPE                  CODIGOOPERACION,
      L_CODIGO_OPE_RET                CODIGOOPERACION,
      SUBSTR(CH.OBSERVACIONCHEQUE,1,15)          DESCRIPCIONREFERENCIA,
      OC.CODIGODIVISA               INDICADORDIVISAREAL,
      '0011'                        ENTIDADOPERACION,
      OC.OFICINAREGISTRO            CODIGOCENTRO,
      OC.USUARIOREGISTRO            CODIGOUSUARIO,
      OC.TERMINALREGISTRO           NUMEROTERMINAL,
      F_HORA_SERVIDOR_HOST(OC.FECHAREGISTRO)             HORAACTUAL,
      NULL                           ENTIDADEMPRESA,
      SIG.ORIGEN                    INDICADORORIGENDINERO,
      NULL                           NOMBREBCOINTER,
      NULL                           DIRECCIONBCOINTER,
      NULL                           NUMERODOCBCOINTER,
      NULL                           NUMEROCTABCOINTER,
      NULL                           INDICADOROPCION,
      P_LOTE_ID                     LOTE_ID,
      OC.USUARIOREGISTRO            USUARIOREGISTRO,
      OC.FECHAREGISTRO            FECHAREGISTRO,
      OC.USUARIOMODIFICACION        USUARIOMODIFICACION,
      OC.FECHAMODIFICACION          FECHAMODIFICACION,
      FN_FORMATO_MONTO_HOST_CI(OC.IMPORTETOTAL)  IMPORTETOTALCEROS
      FROM OPERACIONCHEQUE OC
      INNER JOIN   OPERACION_SIGNIFICATIVA SIG ON (SIG.CODIGOOPERACION = OC.CODIGOOPERACION)
      INNER JOIN CHEQUE CH                  ON(CH.CODIGOOPERACION=OC.CODIGOOPERACION)
      LEFT JOIN   ENTRADACLIENTE EC ON(EC.CODIGOIDENTIFICACION=SIG.TIPO_DOCU_BENE
      AND SIG.NUMERO_DOCU_BENE=EC.NUMEROIDENTIFICACION||NVL(EC.DIGITOIDENTIFICACION,''))
      INNER JOIN TRANSACCION  T ON(T.CODIGOTRANSACCION=OC.CODIGOTRANSACCION)
      WHERE OC.CODIGOTRANSACCION='6'
      AND OC.flagextorno=0;
-- PAGO CHEQUE



END;

PROCEDURE SP_CARGAR_EXP_PAGO(P_LOTE_ID IN INTEGER)
IS
  L_FLAGHOST NUMBER := 0;
BEGIN
SELECT NVL(trans.FLAGENVIARHOST,0) INTO L_FLAGHOST
 from conneg.transaccion trans
 where trans.codigotransaccion = G_TRANSACCION_PAGO_CUENTA_TARJ;

IF L_FLAGHOST = 1 THEN
  INSERT INTO SALIDAPAGO (
    SECUENCIAL_ID, BIN_TARJETA, NUMERO_TARJETA, DIVISA, FECHA_PAGO, HORA_PAGO, IMPORTE_PAGO, INDICADOR_ITF,
    MONTO_ITF, IMPORTE_PAGO_TOTAL, OFICINA_PAGO, USUARIO, TERMINAL,
    LOTE_ID,
    USUARIOREGISTRO,
    FECHAREGISTRO,
    USUARIOMODIFICACION,
    FECHAMODIFICACION,
    IMPORTE_PAGO_CEROS,
    MONTO_ITF_CEROS
  )
  SELECT SQ_SALIDAPAGO.NEXTVAL     SECUENCIAL_ID,
         OPE.BINTARJETA    BIN_TARJETA,
         OPE.NUMEROTARJETA NUMERO_TARJETA,
         OPE.CODIGODIVISA  DIVISA,
         TO_CHAR(OPE.FECHAREGISTRO, 'DD-MM-YYYY') FECHA_PAGO,
         TO_CHAR(OPE.FECHAREGISTRO, 'HH24:MI:SS') HORA_PAGO,
         ope.importeingresado PAGO_TARJETA,
        -- FN_FORMATO_MONTO_HOST((OPE.IMPORTETOTAL - OPE.ITF)) PAGO_TARJETA,
         ' ' INDICADOR_ITF,
         --FN_INDICADOR_ITF(ope.importeingresado, ope.importe) INDICADOR_ITF,--16CARAC
         FN_FORMATO_MONTO_HOST(OPE.ITF) ITF,
         OPE.IMPORTETOTAL,
         OPE.OFICINAREGISTRO,
         USUARIOREGISTRO,
         TERMINALREGISTRO,
         P_LOTE_ID,
         USER,
         SYSDATE,
         USER,
         SYSDATE,
         FN_FORMATO_MONTO_HOST_CI(OPE.IMPORTEINGRESADO)   IMPORTE_PAGO_CEROS,
         FN_INDICADOR_ITF(ope.importeingresado, ope.importe) MONTO_ITF_CEROS
         --FN_FORMATO_MONTO_HOST_CI(OPE.ITF) MONTO_ITF_CEROS
  FROM   OPERACIONEFECTIVO OPE
  WHERE  OPE.CODIGOTRANSACCION = G_TRANSACCION_PAGO_CUENTA_TARJ AND OPE.FLAGEXTORNO = 0;
  COMMIT;

END IF;
END;

PROCEDURE SP_CARGAR_EXP_BLOQUEO(P_LOTE_ID IN INTEGER)
IS
  L_FLAGHOST NUMBER := 0;
BEGIN

SELECT NVL(trans.FLAGENVIARHOST,0) INTO L_FLAGHOST
 from conneg.transaccion trans
 where trans.codigotransaccion = G_TRANSACCION_TARJETA_BLOQUEO;

IF L_FLAGHOST = 1 THEN
--EXPORTACION DE TARJETAS BLOQUEADAS
INSERT INTO SalidaBloqueo (SECUENCIAL_ID,ENTIDAD_CONTRATO,OFICINA_CONTRATO,TIPO_CONTRATO,NUMERO_CONTRATO,BIN_TARJETA,NUMERO_TARJETA,TIPO_TARJETA,SITUACION_TARJETA,TIPO_DOCUMENTO,NUMERO_DOCUMENTO,NOMBRES,INDICADOR_REPOSICION,ACCION,MOTIVO,INDICADOR_ENVIO,OFICINA_ENVIO,QUIEN_REPORTA,DESCRIPCION,NUMERO_BLOQUEO,CLAVE_NOTIFICACION,FECHA_BLOQUEO,HORA_BLOQUEO,USUARIO,TERMINAL,LOTE_ID)
SELECT SQ_SALIDABLOQUEO.NEXTVAL SECUENCIAL_ID,
       BLO.ENTIDADCONTRATO       ENTIDAD_CONTRATO,
       BLO.OFICINACONTRATO       OFICINA_CONTRATO,
       BLO.TIPOCONTRATO          TIPO_CONTRATO,
       BLO.NUMEROCONTRATO        NUMERO_CONTRATO,
       BLO.CODIGOBIN             BIN_TARJETA,
       BLO.NUMEROTARJETA         NUMERO_TARJETA,
       BLO.TIPOTARJETA           TIPO_TARJETA,
       BLO.SITUACIONTARJETA       SITUACION_TARJETA,
       BLO.CODIGOIDENTIFICACION  TIPO_DOCUMENTO,
      -- BLO.NUMEROIDENTIFICACION  NUMERO_DOCUMENTO,
      SUBSTR(BLO.NUMEROIDENTIFICACION,1,10)  NUMERO_DOCUMENTO,
       BLO.TITULAR               NOMBRES,
       BLO.FLAGREPOSICION        INDICADOR_REPOSICION,
       BLO.NOMBREACCION          ACCION,
       BLO.NOMBREMOTIVO          MOTIVO,
       0                         INDICADOR_ENVIO,
       --BLO.FLAGOFICINA           OFICINA_ENVIO,
       --BLO.CODIGOOFICINA         OFICINA_ENVIO,
       BLO.OFICINA_ID               OFICINA_ENVIO,
       BLO.DESCRIPCIONQUIENREPORTA QUIEN_REPORTA,
       BLO.DESCRIPCIONBLOQUEO    DESCRIPCION,
       BLO.NUMERO_BLOQUEO        NUMERO_BLOQUEO,
       SUBSTR(BLO.DESCRIPCIONCLAVE,1,10)      CLAVE_NOTIFICACION,
       TO_CHAR(BLO.FECHAREGISTRO, 'YYYY-MM-DD') FECHA_BLOQUEO,
       TO_CHAR(BLO.FECHAREGISTRO, 'HH24:MM:SS')   HORA_BLOQUEO,
       BLO.USUARIOREGISTRO       USUARIO,
       BLO.TERMINAL_ID           TERMINAL,
       P_LOTE_ID
FROM   OPERACIONBLOQUEO BLO
INNER JOIN ENTRADATARJETA ET ON(BLO.CODIGOOPERACION=ET.OPERACION_BLOQUEO);
COMMIT;
END IF;
/*
SELECT SQ_SALIDABLOQUEO.NEXTVAL SECUENCIAL_ID,
       CON.ENTIDADCONTRATO       ENTIDAD_CONTRATO,
       CON.OFICINACONTRATO       OFICINA_CONTRATO,
       CON.TIPOCONTRATO          TIPO_CONTRATO,
       CON.NUMEROCONTRATO        NUMERO_CONTRATO,
       TAR.BINTARJETA            BIN_TARJETA,
       TAR.NUMEROTARJETA         NUMERO_TARJETA,
       TAR.TIPOTARJETA           TIPO_TARJETA,
       TAR.INDICADORSITUACION    SITUACION_TARJETA,
       CLI.CODIGOIDENTIFICACION  TIPO_DOCUMENTO,
       CLI.NUMEROIDENTIFICACION  NUMERO_DOCUMENTO,
       TRIM(NVL(TRIM(CLI.NOMBRECLIENTE), ' ')||' '||NVL(TRIM(CLI.PRIMERAPELLIDO), ' ')||' '||NVL(TRIM(CLI.SEGUNDOAPELLIDO), ' ')) AS NOMBRES,
       '1'                       INDICADOR_REPOSICION,
       'ACCION'                  ACCION,
       'MOTIVO'                  MOTIVO,
       1                         INDICADOR_ENVIO,
       1                         OFICINA_ENVIO,
       'QUIEN REPORTA'           QUIEN_REPORTA,
       'DESCRIPCION'             DESCRIPCION,
       0                         NUMERO_BLOQUEO,
       0                         CLAVE_NOTIFICACION,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD') FECHA_BLOQUEO,
       TO_CHAR(SYSDATE, 'HH:MM:SS')   HORA_BLOQUEO,
       'USUARIO'                 USUARIO,
       'TERMINAL'                TERMINAL,
       P_LOTE_ID
FROM   ENTRADATARJETA TAR,
       ENTRADACONTRATO CON,
       ENTRADACLIENTECUENTA CCU,
       ENTRADACLIENTE CLI,
       OPERACIONBLOQUEO BLO
WHERE  TAR.TIPOCONTRATO    = '50'
AND    TRIM(TAR.BINTARJETA || TAR.NUMEROTARJETA) = BLO.NUMEROTARJETA
AND    TAR.TIPOCONTRATO    = CON.TIPOCONTRATO
AND    TAR.ENTIDADCONTRATO = CON.ENTIDADCONTRATO
AND    TAR.OFICINACONTRATO = CON.OFICINACONTRATO
AND    TAR.NUMEROCONTRATO  = CON.NUMEROCONTRATO

AND    CCU.CODIGOSERVICIO = TAR.TIPOCONTRATO
AND    CCU.ENTIDADCUENTA  = CON.ENTIDADCONTRATO
AND    CCU.OFICINACUENTA  = CON.OFICINACONTRATO
AND    CCU.NUMEROCONTRATO = CON.NUMEROCONTRATO

AND    CCU.NUMEROCLIENTE  = CLI.NUMEROCLIENTE;
*/

END;

PROCEDURE SP_EXPORTACION_CARGAR (P_ESTADO_ID OUT INTEGER, P_ARCHIVO OUT SYS_REFCURSOR)
IS
  L_ENVIO_ID  INTEGER;
BEGIN

  SELECT MAX(ENV.ENVIO_ID) INTO L_ENVIO_ID FROM ENVIO_HOST ENV;

  IF L_ENVIO_ID IS NULL THEN
    P_ESTADO_ID := 601;
  ELSE
    SELECT ENV.ESTADO_ID INTO P_ESTADO_ID FROM ENVIO_HOST ENV WHERE ENV.ENVIO_ID = L_ENVIO_ID;
  END IF;

  OPEN P_ARCHIVO
  FOR
  SELECT PRO.NOMBRE        NOMBRE_PROCESO,
         TO_CHAR(ARC.FECHAINICIO, 'DD/MM/YYYY HH24:MI:SS')   FECHA_INI,
         TO_CHAR(ARC.FECHAFIN, 'DD/MM/YYYY HH24:MI:SS')      FECHA_FIN,
         TO_CHAR((ARC.FECHAFIN - ARC.FECHAINICIO)/(24*60),'9999999') DURACION,
         ARC.NOMBREARCHIVO NOMBRE_ARCHIVO,
         ARC.DESCRIPCION   ESTADO_ARCHIVO,
         ARC.CANTIDADREGISTROS LINEAS_ARCHIVO
  FROM   (
          SELECT ARC.TIPO_PROCESO_ID, PAR.DESCRIPCION NOMBRE FROM ARCHIVO ARC, PARAMETRO PAR
          WHERE  PAR.CODIGOTIPOPARAMETRO = 35
          AND    ARC.TIPO_PROCESO_ID = PAR.CODIGOPARAMETRO
          AND    ARC.TIPOARCHIVO = 2
         ) PRO
         LEFT JOIN
         (
         SELECT ARC.TIPO_PROCESO_ID, LAR.FECHAINICIO, LAR.FECHAFIN, LAR.NOMBREARCHIVO, PAR.DESCRIPCION, LAR.CANTIDADREGISTROS
         FROM   ENVIO_HOST_LOTE EHL, LOTEARCHIVO LAR, ARCHIVO ARC, PARAMETRO PAR
         WHERE  EHL.ENVIO_ID        = L_ENVIO_ID
         AND    EHL.LOTE_ID         = LAR.IDLOTE
         AND    ARC.CODIGOARCHIVO   = LAR.CODIGOARCHIVO
         AND    PAR.CODIGOTIPOPARAMETRO = 13
         AND    PAR.VALORNUMERICO       = LAR.ESTADO
         ) ARC
ON       PRO.TIPO_PROCESO_ID = ARC.TIPO_PROCESO_ID;
END;

PROCEDURE SP_EXPORTACION_GRABAR(P_USUARIO IN VARCHAR2, P_ERROR OUT VARCHAR2)
IS
  L_ENVIO_ID INTEGER;
  L_LOTE_ID  INTEGER;
  L_ESTADO   INTEGER;
  L_INICIO_APLICACION_ID INTEGER;
BEGIN

  --1.- VALIDAMOS EL ESTADO DE LA APLICACION
  --1.1.- OBTENEMOS EL ID DEL ULTIMO INICIO DE APLICACION
  SELECT MAX(CODIGOINICIO) INTO L_INICIO_APLICACION_ID FROM INICIOAPLICACION;
  --1.2.- SI NO SE HA REGISTRADO ALGUN INICIO
  IF L_INICIO_APLICACION_ID = NULL THEN
    P_ERROR := 'No existe algún registro del inicio de la aplicación';
    RETURN;
  END IF;
  --1.3.- SI EL ESTADO ES DIFERENTE QUE EL TERMINADO
  SELECT FLAGESTADO INTO L_ESTADO FROM INICIOAPLICACION WHERE CODIGOINICIO = L_INICIO_APLICACION_ID;
  IF L_ESTADO NOT IN (2,3) THEN
    P_ERROR := 'La aplicación no ha sido terminada';
    RETURN;
  END IF;

  --2.- REALIZAMOS LA EXPORTACIÓN DE DATOS
  UPDATE INICIOAPLICACION
  SET    FECHAEXPORTACION   = SYSDATE,
         USUARIOEXPORTACION = P_USUARIO,
         FLAGESTADO         = 3
  WHERE  CODIGOINICIO       = L_INICIO_APLICACION_ID;

  SELECT SQ_ENVIOHOST.NEXTVAL INTO L_ENVIO_ID FROM DUAL;

  INSERT INTO ENVIO_HOST (ENVIO_ID,FECHA_INI,ESTADO_ID,USUARIO_CREACION,FECHA_CREACION)
  VALUES (L_ENVIO_ID, SYSDATE, 602, P_USUARIO, SYSDATE);

  COMMIT;

  FOR PROCESO IN (
    SELECT 11 ARCHIVO_ID FROM DUAL UNION
    SELECT 12 ARCHIVO_ID FROM DUAL UNION
    SELECT 13 ARCHIVO_ID FROM DUAL UNION
    SELECT 14 ARCHIVO_ID FROM DUAL
  )
  LOOP
    BEGIN
    L_LOTE_ID := NULL;

    P_GENERAR_ARCHIVO(PROCESO.ARCHIVO_ID, L_LOTE_ID);--14

    EXCEPTION
    WHEN OTHERS THEN
      NULL;
    END;

    --insert into conneg.testmane (dato1) values(L_LOTE_ID);

    IF L_LOTE_ID IS NOT NULL THEN
      INSERT INTO ENVIO_HOST_LOTE (ENVIO_ID,LOTE_ID,USUARIO_CREACION,FECHA_CREACION,USUARIO_MODIFICACION,FECHA_MODIFICACION)
      VALUES (L_ENVIO_ID,L_LOTE_ID,P_USUARIO,SYSDATE,P_USUARIO,SYSDATE);
    END IF;
  END LOOP;

  UPDATE ENVIO_HOST
  SET    FECHA_FIN            = SYSDATE,
         ESTADO_ID            = 603,
         USUARIO_MODIFICACION = P_USUARIO,
         FECHA_CREACION       = SYSDATE
  WHERE  ENVIO_ID             = L_ENVIO_ID;

  COMMIT;
END;

PROCEDURE SP_VERIFICAR_INICIO_APP(P_RESULTADO OUT VARCHAR2, P_ERROR OUT VARCHAR2)
IS
  L_INICIO_APLICACION_ID NUMBER(16);
  L_ACTIVO NUMBER(5);
  L_FLAG_PARAMETRO VARCHAR2(100):='0';
BEGIN
  P_ERROR := 'SIN ERROR';
  P_RESULTADO := '0';
  --OBTENEMOS EL ID DEL ULTIMO INICIO DE APLICACION
  SELECT MAX(CODIGOINICIO) INTO L_INICIO_APLICACION_ID
  FROM INICIOAPLICACION;

  --OBTENEMOS EL ESTADO DEL INICIO DE LA APLICACION
  IF L_INICIO_APLICACION_ID IS NOT NULL THEN
    SELECT FLAGACTIVO INTO L_ACTIVO FROM INICIOAPLICACION
    WHERE  CODIGOINICIO = L_INICIO_APLICACION_ID;
  END IF;
  -- OBTENEMOS FLAG DE CONFIGURACION
  BEGIN
    SELECT NVL(P.VALORALFANUMERICO,'0') into L_FLAG_PARAMETRO FROM PARAMETRO P
    WHERE P.CODIGOTIPOPARAMETRO = 47
    AND ROWNUM = 1;
  EXCEPTION WHEN OTHERS THEN
      L_FLAG_PARAMETRO := '0';
  END;

  --SI EL ESTADO ES ACTIVO
  IF L_ACTIVO = 1 AND L_FLAG_PARAMETRO = '1'THEN
    P_RESULTADO := '1';
  ELSE
    P_RESULTADO := '0';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    P_RESULTADO := '-1';
    P_ERROR := SUBSTR(SQLERRM,1,100);
END;

PROCEDURE SP_DATOS_CORREO_ARCHIVOS(
P_CODIGO_ARCHIVO  INTEGER,
P_NOMBRE_ARCHIVO  OUT VARCHAR2,
P_RESULTADO_CARGA OUT VARCHAR2,
P_FEC_HORA_INICIO OUT DATE,
P_FEC_HORA_FIN    OUT DATE,
P_CORREO          OUT VARCHAR2
)
IS

BEGIN
    SELECT A.CORREOELECTRONICO INTO P_CORREO
    FROM   ARCHIVO A
    WHERE  CODIGOARCHIVO = P_CODIGO_ARCHIVO;

    SELECT NOMBREARCHIVO,ESTADO,FECHAINICIO,FECHAFIN
    INTO   P_NOMBRE_ARCHIVO,P_RESULTADO_CARGA,P_FEC_HORA_INICIO,P_FEC_HORA_FIN
    FROM   LOTEARCHIVO
    WHERE  CODIGOARCHIVO=P_CODIGO_ARCHIVO
    AND IDLOTE=(SELECT MAX(IDLOTE) FROM LOTEARCHIVO WHERE CODIGOARCHIVO=P_CODIGO_ARCHIVO )
    group by NOMBREARCHIVO, ESTADO, FECHAINICIO, FECHAFIN ;

    IF P_RESULTADO_CARGA = '2' THEN
      P_RESULTADO_CARGA := 'EXITOSO';
    ELSE
      P_RESULTADO_CARGA := 'ERRADO';
    END IF;
END;

FUNCTION F_HORA_SERVIDOR_HOST(P_FECHA DATE)RETURN VARCHAR2
IS
P_FECHA_SALIDA VARCHAR2(26);
BEGIN
  --SELECT TO_CHAR(P_FECHA,'YYYY-MM-DD HH24.MI.SS.FFFF') INTO P_FECHA_SALIDA  FROM DUAL;
  SELECT TO_CHAR(P_FECHA,'YYYY-MM-DD-HH24.MI.SS.')||'000000' INTO P_FECHA_SALIDA  FROM DUAL;
  RETURN P_FECHA_SALIDA;
END;

FUNCTION FN_FORMATO_MONTO_HOST(P_NUMERO NUMBER)
  RETURN VARCHAR2
IS
BEGIN
  RETURN REPLACE(''||TRIM(TO_CHAR(P_NUMERO, '999999999999990.99')),'.','');
END;

FUNCTION FN_FORMATO_MONTO_HOST_CI(P_NUMERO NUMBER)
  RETURN VARCHAR2
IS
P_NUMERO_CASTING VARCHAR2(14);
P_NUMERO_CEROS VARCHAR2(17);
BEGIN
  P_NUMERO_CASTING :=  REPLACE(''||TRIM(TO_CHAR(P_NUMERO, '999999999999990.99')),'.','');
  P_NUMERO_CEROS :=  LPAD(P_NUMERO_CASTING,17,0);
  RETURN P_NUMERO_CEROS;
END;

FUNCTION FN_FORMATO_MONTO_HOST_CI_C(P_NUMERO NUMBER)
  RETURN VARCHAR2
IS
P_NUMERO_CASTING VARCHAR2(14);
P_NUMERO_CEROS VARCHAR2(17);
BEGIN
  P_NUMERO_CASTING :=  REPLACE(''||TRIM(TO_CHAR(P_NUMERO, '999999999999990.99')),'.','');
  P_NUMERO_CEROS :=  LPAD(P_NUMERO_CASTING,15,0);
  RETURN P_NUMERO_CEROS;
END;

FUNCTION FN_INDICADOR_ITF(P_NUMERO1 NVARCHAR2,P_NUMERO2 NVARCHAR2 )
  RETURN NVARCHAR2
IS
P_NUMERO NVARCHAR2(17);
BEGIN
    IF TRIM(P_NUMERO1) = TRIM(P_NUMERO2)  THEN
      P_NUMERO := '00000000000000000';
    ELSE
        P_NUMERO := '00000000000000001';
    END IF;

    RETURN P_NUMERO;
END;

FUNCTION FN_OPERACION_HOST(P_NUMERO NUMBER)
  RETURN NVARCHAR2
IS
 P_CODIGO_OPE_HOST NVARCHAR2(3);
BEGIN
  IF TRIM(P_NUMERO) = 3 THEN  -- DEPOSITO
    SELECT VALORALFANUMERICO INTO P_CODIGO_OPE_HOST FROM PARAMETRO WHERE CODIGOPARAMETRO = 5080 AND CODIGOTIPOPARAMETRO = 50;
  ELSE
    SELECT VALORALFANUMERICO INTO P_CODIGO_OPE_HOST FROM PARAMETRO WHERE CODIGOPARAMETRO = 5081 AND CODIGOTIPOPARAMETRO = 50;
  END IF;
  RETURN P_CODIGO_OPE_HOST;
END;

FUNCTION FN_OPERACION_CODIGO(P_NUMERO NUMBER)
  RETURN NVARCHAR2
IS
 P_CODIGO_OPE NVARCHAR2(3);
BEGIN
  IF TRIM(P_NUMERO) = 3 THEN  -- DEPOSITO
    SELECT VALORALFANUMERICO INTO P_CODIGO_OPE FROM PARAMETRO WHERE CODIGOPARAMETRO = 4201 AND CODIGOTIPOPARAMETRO = 42;
  ELSE
    SELECT VALORALFANUMERICO INTO P_CODIGO_OPE FROM PARAMETRO WHERE CODIGOPARAMETRO = 4202 AND CODIGOTIPOPARAMETRO = 42;

  END IF;
  RETURN P_CODIGO_OPE;
END;

FUNCTION FN_EXISTE_ARCHIVO_HOST_DIARIO(P_ID_ARCHIVO NUMBER)
  RETURN BOOLEAN
IS
lb_file_exist boolean;
ln_size number;
ln_block_size number;
LC_RUTA_ARCHIVO    VARCHAR2(2000);
LC_NOM_ARCHIVO     VARCHAR2(2000);
begin
    SELECT NOMBREARCHIVO || TO_CHAR(SYSDATE, 'yymmdd') || '.' ||
           EXTENSION,
           RUTASALIDA
           INTO
           LC_NOM_ARCHIVO, LC_RUTA_ARCHIVO
    FROM ARCHIVO
    WHERE CODIGOARCHIVO = P_ID_ARCHIVO;

    sys.utl_file.fgetattr(LC_RUTA_ARCHIVO,LC_NOM_ARCHIVO,lb_file_exist,ln_size,ln_block_size);

    IF lb_file_exist THEN
      RETURN TRUE;
    END IF;
  RETURN FALSE;
end;

--MODIFICAR PROCEDURE EN PQ_ARCHIVO Body
PROCEDURE P_AUTOGENERAR_ARCHIVOS_HOST(P_PROG_ARCHIVO VARCHAR, P_EXITO OUT NUMBER) IS
    LC_RUTA_ARCHIVO    VARCHAR2(2000);
    LC_NOM_ARCHIVO     VARCHAR2(2000);
    LC_PROG_ARCHIVO VARCHAR2(10);
    LB_ARCHIVO_ABIERTO BOOLEAN DEFAULT FALSE;
    ARCHIVO            UTL_FILE.FILE_TYPE;
  BEGIN

  FOR PROCESO IN (
    SELECT 11 ARCHIVO_ID FROM DUAL UNION
    SELECT 12 ARCHIVO_ID FROM DUAL UNION
    SELECT 13 ARCHIVO_ID FROM DUAL UNION
    SELECT 14 ARCHIVO_ID FROM DUAL
  )
  LOOP
    --dbms_output.put_line(PROCESO.ARCHIVO_ID);
    IF NOT FN_EXISTE_ARCHIVO_HOST_DIARIO(PROCESO.ARCHIVO_ID) then
      SELECT NOMBREARCHIVO || TO_CHAR(SYSDATE, 'yymmdd') || '.' ||EXTENSION,
           RUTASALIDA
           INTO
           LC_NOM_ARCHIVO, LC_RUTA_ARCHIVO
      FROM ARCHIVO
      WHERE CODIGOARCHIVO = PROCESO.ARCHIVO_ID;

      SELECT  PROGRAMACION_HORA || PROGRAMACION_MINUTO INTO LC_PROG_ARCHIVO
      FROM ARCHIVO
      WHERE
      NOMBREARCHIVO = 'CRONTRIGGER';

      IF NOT LB_ARCHIVO_ABIERTO AND LC_PROG_ARCHIVO = P_PROG_ARCHIVO THEN
          --dbms_output.put_line(LC_NOM_ARCHIVO);
          ARCHIVO            := UTL_FILE.FOPEN(LC_RUTA_ARCHIVO,LC_NOM_ARCHIVO,'w');
          LB_ARCHIVO_ABIERTO := TRUE;
      END IF;

      IF LB_ARCHIVO_ABIERTO THEN
        UTL_FILE.FCLOSE(ARCHIVO);
        LB_ARCHIVO_ABIERTO := FALSE;
        --dbms_output.put_line(LC_NOM_ARCHIVO || ' CERRADO');
      END IF;
    end if;
  END LOOP;
  P_EXITO:=1;
  END P_AUTOGENERAR_ARCHIVOS_HOST;
END PQ_ARCHIVO;
/

spool off